package com.wurao.util;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ImportExcel {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    //判断Excel的版本,获取Workbook
    public static Workbook getWorkbok(InputStream in,File file) throws IOException{
        Workbook wb = null;
        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003
            wb = new HSSFWorkbook(in);
        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    //判断文件是否是excel
    public static void checkExcelVaild(File file) throws Exception{
        if(!file.exists()){
            throw new Exception("文件不存在");
        }
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){
            throw new Exception("文件不是Excel");
        }
    }

    //由指定的Sheet导出至List
    public  Map<String,String> exportListFromExcel(String url,int sheetnum) throws Exception {
        List<Map<String,String>> list=new ArrayList<>();
        Map<String,String> map=new HashMap<>();
        // 同时支持Excel 2003、2007
        File excelFile = new File( url);
        FileInputStream is = new FileInputStream(excelFile);
        //效验文件是否存在
        checkExcelVaild(excelFile);
        Workbook workbook = getWorkbok(is,excelFile);

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            // Sheet的数量
            int sheetCount = workbook.getNumberOfSheets();
            // 遍历第一个Sheet 设置当前excel中sheet的下标：0开始
            Sheet sheet = workbook.getSheetAt(sheetnum);
            // 为跳过第一行目录设置count
            int count = 0;
            for (Row row : sheet) {
                // 跳过第一行的目录
                if(count == 0){
                    count++;
                    continue;
                }else{
                    //存入map的键标识
                    count++;
                }
                // 如果当前行没有数据，跳出循环
                if(row==null){
                    break;
                }
                String rowValue = "";

                //for (Cell cell : row) {
                int cellType=0;
                for (int j=0;j<row.getLastCellNum();j++) {
                    Cell cell=row.getCell(j);
                    try{
                        //如果列是空就报错
                        cellType = cell.getCellType();
                    }catch(Exception e){
                        //默认为空
                        cellType=Cell.CELL_TYPE_BLANK;
                    }
//                    if(cell == null){
//                        continue;
//                    }
                    String cellValue = "";
                    switch (cellType) {
                        // 文本
                        case Cell.CELL_TYPE_STRING:
                            cellValue = cell.getRichStringCellValue().getString() + "#";
                            break;
                        // 数字、日期
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()) + "#";
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "#";
                            }
                            break;
                        // 布尔型
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = String.valueOf(cell.getBooleanCellValue()) + "#";
                            break;
                        // 空白
                        case Cell.CELL_TYPE_BLANK:
                            cellValue =  "null#";
                            break;
                        // 错误
                        case Cell.CELL_TYPE_ERROR: // 错误
                            cellValue = "error#";
                            //cellValue = String.valueOf(cell.getBooleanCellValue()) + "#";
                            break;
                        // 公式
                        case Cell.CELL_TYPE_FORMULA:
                            // 得到对应单元格的公式
                            //cellValue = cell.getCellFormula() + "#";
                            // 得到对应单元格的字符串
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "#";
                            break;
                        default:
                            cellValue = "空#";
                    }
                    //System.out.print(cellValue);
                    rowValue += cellValue;
                }
                System.out.println(rowValue);
                System.out.println();
                map.put(String.valueOf(count),rowValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            return map;
        }
    }

    public static void main(String[] args) throws Exception {
       ImportExcel importExcel=new ImportExcel();
       importExcel.exportListFromExcel( "C:\\Users\\pc\\Desktop\\123.xlsx", 0);
    }
}